const sql = require('mssql');

/**
 * 
 * 供应商编码匹配系统
 */

/**
 * 
 * 添加供应商编码匹配 
 */
const addSupplierNum = async (ctx, next) => {
    const body = ctx.request.body;
    if (body.px) {
        let updateSql = `UPDATE GYSBMPP SET YPBM='${body.YPBM}' WHERE BM = '${body.BM}' AND GYPBM = '${body.GYPBM}'`;
        let updateResult = await sql.query(updateSql);
        if (updateResult.rowsAffected[0] === 1) {
            ctx.body = { success: 4 }
        } else {
            ctx.body = { success: 5 }
        }
    } else {
        let isExistSql = `SELECT COUNT(BM) AS val FROM GYSBMPP WHERE BM = '${body.BM}' AND GYPBM = '${body.GYPBM}'`;
        let isExist = await sql.query(isExistSql);
        if (isExist.recordset[0].val > 0) {
            ctx.body = { success: 1 };
        } else {
            let insertSql = `INSERT INTO GYSBMPP VALUES('${body.YPBM}','${body.BM}','${body.GYPBM}')`;
            let result = await sql.query(insertSql);
            if (result.rowsAffected[0] === 1) {
                ctx.body = { success: 2 }
            } else {
                ctx.body = { success: 3 }
            }
        }
    }

}

/**
 * 
 * 删除供应商编码匹配 
 */
const deleteSupplierNum = async (ctx, next) => {
    const body = ctx.request.body;
    let deleteSql = `DELETE FROM GYSBMPP WHERE BM = '${body.BM}' AND GYPBM = '${body.GYPBM}'`;
    let result = await sql.query(deleteSql);
    if (result.rowsAffected[0] === 1) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }

}


/**
 * 
 * 获取供应商编码匹配 
 */
const getsupplierNum = async (ctx, next) => {
    const body = ctx.request.body;
    let searchVal = body.searchVal.ypbm.val;
    let totalSql = `SELECT COUNT(YPBM) AS total FROM GYSBMPP `;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY BM) AS px,* FROM GYSBMPP `;
    if (searchVal) {
        startSql += ` WHERE BM = '${searchVal}' OR GYPBM = '${searchVal}' OR YPBM = '${searchVal}'`;
        totalSql += ` WHERE BM = '${searchVal}' OR GYPBM = '${searchVal}' OR YPBM = '${searchVal}'`;
    }
    startSql += `) AS t1 WHERE t1.px BETWEEN ${(body.pageNum - 1) * 10 + 1} AND ${body.pageNum * 10}`;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = { result: result.recordset,total:total.recordset[0].total }
}

/**
 * 
 * 获取所有供应商编码
 */
const getAllSupplierNum = async (ctx, next) => {
    
    let startSql = `SELECT BM,MC,JP FROM BM_WLDW WHERE DWLX =1 AND QY_FLAG='T'`;
    
    let result = await sql.query(startSql);
    ctx.body = { result: result.recordset }
}

/**
 * 
 * 获取筛选的商编码 
 */
const getAllShopSupplierNum = async (ctx, next) => {
    const body = ctx.request.body;
   
    let startSql = '';
    if (body.val) {
         startSql = `SELECT BM,TM,MC,JLDW,SCCJ,PZWH,YPLX,GG FROM BM_YP WHERE QY_FLAG = 'T' AND (BM LIKE '%${body.val}%' OR TM LIKE '%${body.val}%' OR MC LIKE '%${body.val}%' OR PZWH LIKE '%${body.val}%' OR JP LIKE '%${body.val}%') `;
    } else if (body.YPBM) {
         startSql = `SELECT BM,TM,MC,JLDW,SCCJ,PZWH,YPLX,GG FROM BM_YP WHERE QY_FLAG = 'T' AND BM = '${body.YPBM}'  `;
    }

    let result = await sql.query(startSql);
  
    ctx.body = { result: result.recordset }
}




module.exports = {
    'POST /getAllShopSupplierNum': getAllShopSupplierNum,
    'GET /getAllSupplierNum': getAllSupplierNum,
    'POST /deleteSupplierNum': deleteSupplierNum,
    'POST /getsupplierNum': getsupplierNum,
    'POST /addSupplierNum': addSupplierNum

}